* ----------------------------------------------------------------------------- *
* Natural Resource Rents, Local Taxes, and Government Performance: Evidence from
* Colombia
* By: Luis R. Martinez
* Name: 11-panel-set-up.do
* Description: This do file constructs the main municipal panel.
* ----------------------------------------------------------------------------- *

clear all 
cls 

*------------------------------------------------------------------------------*
**# 1. Panel CEDE 2014:
*------------------------------------------------------------------------------*
	
use "${dpraw}\01-panel-cede\panel cede.dta", clear

	
** housekeeping
rename pobl_tot pop_total
rename pobl_urb pop_cabecera
rename pobl_rur pop_resto
rename ano year
rename y_total y_total
rename y_corr y_corr 
rename y_corr_tribut y_tax
rename y_corr_tribut_predial y_tax_pred
rename y_corr_tribut_IyC y_tax_ica 
rename y_corr_tribut_gasol y_tax_gas
rename y_corr_tribut_otros_1 y_tax_otr_pre00
rename y_corr_tribut_otros_2 y_tax_otr_post00
rename y_no_tribut y_notax 
rename y_transf y_trf 
rename y_transf_nal y_trf_nal 
rename y_transf_otra y_trf_otr 
rename g_total g_total 
rename g_corr g_corr 
rename g_func g_fct 
rename g_func_personal g_svc 
rename g_func_general g_gen 
rename g_func_trans g_trf 
rename g_int_deudap g_int_debt 
rename y_cap y_k 
rename y_cap_regalias y_k_rega 
rename y_cap_transf y_k_trf 
rename y_cap_cofinan y_k_cofin 
rename y_cap_otros y_k_otr 
rename g_cap i 
rename g_cap_FBKF i_fbkf 
rename g_cap_resto i_otr 
rename deficit_total def_total 
rename finan f 
rename finan_credito f_cred 
rename finan_credito_desemb f_cred_in 
rename finan_credito_amort f_cred_out 
rename finan_credito_balance f_otr 
rename ing_func pct_fnc 
rename deuda magn_debt
rename ing_trans pct_trf_y 
rename ing_propios pct_own_y 
rename gast_inv pct_i_g 
rename ahorro cap_aho 
rename desemp_fisc IDF 
rename p_dep rank_prv 
rename p_nal rank_nal 


** Fixing Altitude:

replace altura=700 if codmpio==5604	//Antioquia	REMEDIOS
replace altura=30 if codmpio==13062	//Bolívar	ARROYOHONDO
replace altura=68 if codmpio==13222	//Bolívar	CLEMENCIA
replace altura=29 if codmpio==13268	//Bolívar	EL PEÑÓN
replace altura=78 if codmpio==13458	//Bolívar	MONTECRISTO
replace altura=60 if codmpio==13490	//Bolívar	NOROSÍ
replace altura=29 if codmpio==13580	//Bolívar	REGIDOR
replace altura=8 if codmpio==13620	//Bolívar	SAN CRISTÓBAL
replace altura=227 if codmpio==13655	//Bolívar	SAN JACINTO DEL CAUCA
replace altura=24 if codmpio==13810	//Bolívar	TIQUISIO
replace altura=700 if codmpio==17495	//Caldas	NORCASIA
replace altura=1710 if codmpio==17665	//Caldas	SAN JOSÉ
replace altura=1100 if codmpio==19300	//Cauca	GUACHENÉ
replace altura=1140 if codmpio==19785	//Cauca	SUCRE
replace altura=982 if codmpio==19845	//Cauca	VILLA RICA
replace altura=1200 if codmpio==20570	//Cesar	PUEBLO BELLO
replace altura=6 if codmpio==23300	//Córdoba	COTORRA
replace altura=50 if codmpio==23350	//Córdoba	LA APARTADA
replace altura=55 if codmpio==23682	//Córdoba	SAN JOSÉ DE URÉ
replace altura=5 if codmpio==23815	//Córdoba	TUCHÍN
replace altura=2242 if codmpio==25019	//Cundinamarca	ALBÁN
replace altura=2600 if codmpio==25260	//Cundinamarca	EL ROSAL
replace altura=2586 if codmpio==25269	//Cundinamarca	FACATATIVÁ
replace altura=2548 if codmpio==25286	//Cundinamarca	FUNZA
replace altura=2554 if codmpio==25430	//Cundinamarca	MADRID
replace altura=1540 if codmpio==25645	//Cundinamarca	SAN ANTONIO DEL TEQUENDAMA
replace altura=2566 if codmpio==25754	//Cundinamarca	SOACHA
replace altura=2663 if codmpio==25769	//Cundinamarca	SUBACHOQUE
replace altura=2600 if codmpio==25799	//Cundinamarca	TENJO
replace altura=2500 if codmpio==25898	//Cundinamarca	ZIPACÓN
replace altura=43 if codmpio==27050	//Chocó	ATRATO
replace altura=105 if codmpio==27086	//Chocó	BELÉN DE BAJIRÁ
replace altura=14 if codmpio==27150	//Chocó	CARMEN DEL DARIÉN
replace altura=43 if codmpio==27160	//Chocó	CÉRTEGUI
replace altura=50 if codmpio==27425	//Chocó	MEDIO ATRATO
replace altura=12 if codmpio==27430	//Chocó	MEDIO BAUDÓ
replace altura=68 if codmpio==27450	//Chocó	MEDIO SAN JUAN
replace altura=5 if codmpio==27495	//Chocó	NUQUÍ
replace altura=70 if codmpio==27580	//Chocó	RÍO IRÓ
replace altura=125 if codmpio==27600	//Chocó	RÍO QUITO
replace altura=78 if codmpio==27810	//Chocó	UNIÓN PANAMERICANA
replace altura=320 if codmpio==44035	//La Guajira	ALBANIA
replace altura=223 if codmpio==44420	//La Guajira	LA JAGUA DEL PILAR
replace altura=45 if codmpio==47030	//Magdalena	ALGARROBO
replace altura=15 if codmpio==47205	//Magdalena	CONCORDIA
replace altura=20 if codmpio==47460	//Magdalena	NUEVA GRANADA
replace altura=45 if codmpio==47660	//Magdalena	SABANAS DE SAN ANGEL
replace altura=20 if codmpio==47720	//Magdalena	SANTA BÁRBARA DE PINTO
replace altura=20 if codmpio==47960	//Magdalena	ZAPAYÁN
replace altura=2 if codmpio==47980	//Magdalena	ZONA BANANERA
replace altura=121 if codmpio==52254	//Nariño	EL PEÑOL
replace altura=50 if codmpio==52480	//Nariño	NARIÑO
replace altura=60 if codmpio==54553	//Norte de Santander	PUERTO SANTANDER
replace altura=5 if codmpio==70221	//Sucre	COVEÑAS
replace altura=174 if codmpio==70233	//Sucre	EL ROBLE
replace altura=100 if codmpio==91460	//Amazonas	MIRITI - PARANÁ
replace altura=120 if codmpio==91530	//Amazonas	PUERTO ALEGRÍA
replace altura=96 if codmpio==91536	//Amazonas	PUERTO ARICA
replace altura=2800 if codmpio==19743		//Cauca SILVIA 	
replace altura=80 if codmpio==94885	//Guainía	LA GUADALUPE
replace altura=206 if codmpio==94887	//Guainía	PANA PANA	
replace altura=97 if codmpio==94663	//Guainía	MAPIRIPANA	
replace altura=115 if codmpio==94888	//Guainía	MORICHAL	

/*
Note: 3 towns lacks altitude data (codmpio==91430 Amazonas LA VICTORIA, and the 
	  2 in San Andres) 															*/

** Fixing distance to capital

replace discapital=263 if codmpio==13490	//Bolívar	NOROSÍ
replace discapital=89 if codmpio==19300		//Cauca	GUACHENÉ
replace discapital=114 if codmpio==23682	//Córdoba	SAN JOSÉ DE URÉ
replace discapital=61 if codmpio==23815		//Córdoba	TUCHÍN

*------------------------------------------------------------------------------*
**# 2. Add SGP transfers
*------------------------------------------------------------------------------*
	
/*
Note: Missing before 1994 plus Belen de Bajira and 20 corregimientos 
	  departamentales. 															*/	

preserve
	import excel "${dpraw}\03-sgp-dnp\dnp-sistema-general-participaciones.xlsx", ///
			firstrow clear	
			
	tab year
	
	keep codmpio year sgp_educacion sgp_propgen sgp_salud sgp_aguapot
	
	tempfile sgp
	save `sgp'
restore 	  

merge 1:1 codmpio year using `sgp'
	drop if _merge==2		//Belen de Bajira
	drop _merge

*** Data lebels:
label var sgp_educacion "Nat. Transfers: Education. Source: DNP"
label var sgp_propgen "Nat. Transfers: General. Source: DNP"
label var sgp_salud "Nat. Transfers: Health. Source: DNP"
label var sgp_aguapot "Nat. Water: Education. Source: DNP"
	
*------------------------------------------------------------------------------*
**# 3. Royalties data
*------------------------------------------------------------------------------*

** DNP Data:
preserve
	import excel "${dpraw}\04-royalties\dnp-regalias-total.xlsx", firstrow clear	
			
	tab Año
	
	rename (CodigodeMunicipio Año TotalRegalias) (codmpio year regalias)
	
	tempfile royalties
	save `royalties'
restore 

merge 1:1 codmpio year using `royalties' 
	mvencode regalias if year>=1994&year<=2011,mv(0) over //385 munis missing
	drop _merge
	label var regalias "Rayalties. Unit: Pesos. Source: DNP-DR"

** ANH Data: 
preserve
	import excel "${dpraw}\04-royalties\anh-regalias-total.xlsx", firstrow clear	
			
	tab Año
	
	rename (CodigodeMunicipio Año RegaliasPetroleo RegaliasPetroleoPuertos) 	///
		   (codmpio year regalias_oil regalias_oil_port)
	
	tempfile royalties
	save `royalties'
restore 

merge 1:1 codmpio year using `royalties' 
	mvencode regalias_oil regalias_oil_port,mv(0) over
	drop _merge
	
*** Labels:
label var regalias_oil "Royalties. Source: ANH"
label var regalias_oil_port "Royalties at Ports. Source: ANH"

	
** Ecopetrol Data:
preserve
	import excel "${dpraw}\04-royalties\ecop-regalias-total.xlsx", firstrow 	///
			clear	
			
	tab Año
	
	rename (CodigodeMunicipio Año RegaliasPetroleo RegaliasPetroleoPuerto) 	///
		   (codmpio year regalias_oil_eco regalias_oil_port_eco)
	
	tempfile royalties
	save `royalties'
restore

merge 1:1 codmpio year using `royalties' 
	mvencode regalias_oil_eco regalias_oil_port_eco,mv(0) over
	label var regalias_oil_eco "Royalties. Unit: Pesos. Source: Ecopetrol"
	label var regalias_oil_port_eco "Royalties at Port. Unit: Pesos. Source: Ecopetrol"
	drop _merge

gen regalias_oil_total=regalias_oil+regalias_oil_port+regalias_oil_eco+regalias_oil_port_eco
	label var regalias_oil_total "Total Royalties. Source: ANH y Ecopetrol"

*------------------------------------------------------------------------------*
**# 4. Cadastral Data:
*------------------------------------------------------------------------------*
	
** Vigencias: 
preserve
	import excel "${dpraw}\05-cadastral-info\igac-informacion-catastral.xlsx", 		///
			firstrow clear	
			
	tab Año
	
	rename (Año CodigodeMunicipio VigenciaUrbana)	///
		   (year codmpio vigenciau)
	
	tempfile cadastral
	save `cadastral'
restore

merge 1:1 codmpio year using `cadastral'
	drop if _merge==2
	drop _merge
	label var vigenciau "Cadastral Year Update - Urban. Source: IGAC"

** Value:
preserve
	import excel "${dpraw}\05-cadastral-info\igac-informacion-catastral-valores.xlsx", 		///
			firstrow clear	
			
	tab Año
	
	rename (Año CodifodeMunicipio VigenciaRural PrediosRural AvalúoRural		///
								  VigenciaUrbano PrediosUrbanos AvalúoUrbano)	///
		   (year codmpio cr nr vr cu nu vu)
	
	tempfile cadastral
	save `cadastral'
restore

merge 1:1 codmpio year using  `cadastral'
	drop if _merge==2&year!=2013	//Aca se estan yendo los datos de 2013
	drop _merge
	replace cu=vigenciau if cu==.&vigenciau!=.
	drop vigenciau
	
*** Labels:
label var cr "Cadastral Year Update - Urban. Source: IGAC"
label var nr "Cadastral Num. Properties - Urban. Source: IGAC"
label var vr "Cadastral Value - Urban. Source: IGAC"
label var cu "Cadastral Year Update - Rural. Source: IGAC"
label var nu "Cadastral Num. Properties - Rural. Source: IGAC"
label var vu "Cadastral Value - Rural. Source: IGAC"	

*------------------------------------------------------------------------------*
**# 5. Education
*------------------------------------------------------------------------------*
	
preserve
	import excel "${dpraw}\07-education\cobertura-PQR-LM-DNP.xlsx", 			///
			firstrow clear	
			
	tab año
	
	drop ndpto nmpio
	
	rename año year

	tempfile educ
	save `educ'
restore	
	
** Add education cover from DNP: 
merge 1:1 codmpio year using `educ'
	drop _merge

/*
Note: Missing 3 new munis (Norosi, San Jose de Ure y Tuchin)					*/	
	
*------------------------------------------------------------------------------*
**# 6. Social Programs
*------------------------------------------------------------------------------*
	
** Add cobrs from MPS	

/*
Note: Missing for the 4 new munis and the 20 corregimientos)					*/
	
preserve
	import excel "${dpraw}\08-social-programs\regimen-subsidiado-Min-Salud.xlsx", ///
			firstrow clear	
			
	tab año
	
	rename (año RegSubsidiadoporSisben) (year cobrs)

	tempfile cob
	save `cob'
restore	
	
merge 1:1 codmpio year using `cob'
	label var cobrs "cob reg subs/sisben12 (123 hasta 06)(MPS)"
	drop if _merge==2
	drop _merge

/*
Note: Missing for the 4 new munis and the 20 corregimientos.					*/	

/*
Note: Only the first year has values above 100% and later years seem to have 
	  been censored. 															*/

replace cobrs=100 if cobrs>100&cobrs!=.	

** Familias en Accion data	(missing for the 20 corregimientos)

preserve
	import excel "${dpraw}\08-social-programs\cobertura-familias-en-accion-DNP.xlsx", ///
			firstrow clear	
			
	tab año
	
	rename (año NuevasFamiliasenAccion) (year faccion)

	tempfile famaccion
	save `famaccion'
restore	
	

merge 1:1 codmpio year using `famaccion'
drop if _merge==2	//Belen de Bajira
drop _merge
label var faccion "New Families in Familias en Accion. Source: DPS"


*------------------------------------------------------------------------------*
**# 7. DNP Aqueduct Data
*------------------------------------------------------------------------------*
	

preserve
	import excel "${dpraw}\09-aqueduct\cobertura-acueducto-DNP-2005.xlsx", 		///
			firstrow clear	
			
	tab año
	
	rename (año) (year)

	tempfile aqueduct
	save `aqueduct'
restore	
		
	
//Data missing for 3 new munis
merge 1:1 codmpio year using `aqueduct'
	drop _merge

/*
Note: Missing for the 3 munis.													*/	

label var acu_tot "Percentage of population with Aqueduct. Source: DNP/Censo"
label var acu_urb "Percentage of urban population with Aqueduct. Source: DNP/Censo"
label var acu_rur "Percentage of rural population with Aqueduct. Source: DNP/Censo"

label var alc_tot "Percentage of population with Sewerage. Source: DNP/Censo"
label var alc_urb "Percentage of urban population with Sewerage. Source: DNP/Censo"
label var alc_rur "Percentage of rural population with Sewerage. Source: DNP/Censo"


*------------------------------------------------------------------------------*
**# 8. Community Councils
*------------------------------------------------------------------------------*

* Consejos Comunitarios Data
merge 1:1 codmpio year using "${dpraw}\10-community-council\CONSEJOS_URIBE.dta"
mvencode cons_com if year>=2002&year<=2010, mv(0) over
drop _merge

label var cons_com "Presidential Community Councils"

*------------------------------------------------------------------------------*
**# 9. Health
*------------------------------------------------------------------------------*

** No need to add TMI as it is already in the CEDE panel
rename TMI tmi // Tasa de Mortalidad Infantil (Infant Mortality Rate)

** Índice de riesgo para la calidad del agua potable (IRCA) from INS
	*** IRCA water quality index

preserve
	import excel "${dpraw}\12-health\ins-irca-07-12.xlsx", 				///
			firstrow clear	
			
	tab Año
	
	rename (CodigodeMunicipio Año IRCA)	///
		   (codmpio year irca)
	
	destring year, replace

	tempfile irca	
	save `irca'
restore

merge 1:1 codmpio year using `irca'
	drop if _merge ==2
	drop _merge	
	
label var irca "IRCA water quality index. Source: INS"	
	
** Death records
merge 1:1 codmpio year using "${dpraw}\12-health\defunciones.dta"
	drop if _merge==2
	drop _merge

** Births records
merge 1:1 codmpio year using "${dpraw}\12-health\births.dta"
	drop if _merge==2
	drop _merge
	
** Vaccines
merge 1:1 codmpio year using "${dpraw}\12-health\vacunas.dta"
	drop _merge
	
*------------------------------------------------------------------------------*
**# 10. Prices
*------------------------------------------------------------------------------*
	
** Add oil prices from IMF
preserve
	import excel "${dpraw}\14-prices\oil-prices-IMF.xlsx", 						///
			firstrow clear	
			
	tempfile oil_p	
	save `oil_p'
restore

merge m:1 year using `oil_p'
	drop if _merge==2	// Years before panel
	rename oil_price price_oil
	drop _merge	// Years 1984 to 1988 have no oil price data
	
	label var price_oil "Oil Price. Source: IFS"
	
*------------------------------------------------------------------------------*
**# 12. Elections:
*------------------------------------------------------------------------------*	

** Add election results:

*** Mayors elections:
merge 1:1 codmpio year using "${dpraw}\15-elections\summary_Alcalde_new.dta"
	drop if _merge==2
	drop _merge

** President elections:
merge 1:1 codmpio year using "${dpraw}\15-elections\summary_Presidente_new.dta"
	drop if _merge==2
	drop _merge
	
 foreach x of varlist myr_* pres_* {
 	gen `x'_eff=`x'[_n-1] if codmpio==codmpio[_n-1]
 	replace `x'_eff=`x'_eff[_n-1] if `x'_eff==.&`x'_eff[_n-1]!=.&				///
 			codmpio==codmpio[_n-1]
 }
 
* This is the original data, which also includes local councils and senate:
gen period=.
	replace period=0 if year>=2001&year<=2003
	replace period=1 if year>=2004&year<=2007
	replace period=2 if year>=2008&year<=2011
	replace period=2 if year>=2012&year<=2015

merge m:1 codmpio period using "${dpraw}\15-elections\summary_local.dta"
	drop if _merge==2
	drop _merge
	drop if coddepto==88 //San Andres y Belen de Bajira se van
	drop if codmpio==88001|codmpio==88564
	
*------------------------------------------------------------------------------*
**# 13. Conflict:
*------------------------------------------------------------------------------*	

preserve 
	use "${dpraw}\06-urosario-conflict\collapse-database_URosario_1996-2014.dta" ///
			, clear
			
	rename municipality codmpio
	
	tempfile conflict
	save `conflict'
restore 

merge 1:1 codmpio year using `conflict'
	mvencode gued farcd elnd pard, mv(0) over
	drop _merge
	
*------------------------------------------------------------------------------*
**# 14. Exchange rate:
*------------------------------------------------------------------------------*	
** TRM:
preserve
	import excel "${dpraw}\16-banrep\TRM-average-BANREP.xlsx", 					///
			firstrow clear	
			
	tempfile trm	
	save `trm'
restore
merge m:1 year using `trm'
	drop if _merge==2	// Years before panel
	drop _merge
	rename TRM trm
	label var trm "Tasa de cambio promedio. Source: Banrep"
	
*------------------------------------------------------------------------------*
**# 14. Housekeeping
*------------------------------------------------------------------------------*
	
#d;
keep 
codmpio municipio year coddepto muni 
indrural nbi cob_eelectr notarias peagrariac peofdereci nacimientos pepsaludc
area altura disbogota discapital D_Coca
y_total y_corr y_tax y_tax_pred y_tax_ica y_tax_gas y_tax_otr_post00 y_notax 
y_trf g_total g_corr g_fct g_svc g_gen g_trf g_int_debt deficit y_k y_k_rega 
y_k_trf y_k_cofin i i_fbkf i_otr def_total f f_cred f_cred_in f_cred_out 
ba_tot_vr
inv_crecusion-inv_en_vivienda sgp_educacion sgp_salud sgp_aguapot sgp_propgen
regalias regalias_oil regalias_oil_port regalias_oil_eco regalias_oil_port_eco 
regalias_oil_total
pop_total cbtr-cntot tmi cobrs irca alumn_pri alumn_mpri alumn_hpri alumn_pri_u 
alumn_pri_r t_establ establ_total  docen_total docen_prej docen_prij 
docen_secj admin_total cob01_bcg cob01_dpt cob01_pol cob1_tv nporvac01 nporvac1
vu vr nu nr pop_total
cnbas tmi cobrs irca acu_tot alc_tot 
vacunas05 eda_ira05 embarazo05 defunciones05 vacunas01 eda_ira01 embarazo01 
defunciones01 nacimientos
low_weight consultas consultas0 consultas4 subsidiado pre_term
gued pard
cu cr
price_oil trm regalias_oil_eco 
myr_ch_prty_eff myr_sh_winner_v_eff myr_sh_vblank_eff myr_left_eff 
myr_left_rwc_eff myr_para_eff myr_lib_eff myr_cons_eff myr_new_parties_eff 
myr_sh_null_r1_eff myr_sh_empty_r1_eff myr_ncandidates_myr_eff myr_hhi_norm_eff 
myr_sh_inc_eff myr_margin_eff gob_myr myr_no_election_eff no_myr no_cons
myr_left myr_lib myr_cons myr_new_parties
pct_myr_cons myr_cons_over_50
pres_sh_vblank_r1_eff 
pres_sh_null_r1_eff pres_sh_empty_r1_eff pres_sh_lib_r1_eff pres_sh_cons_r1_eff
pres_sh_winner_r1_eff pres_sh_left_r1_eff pres_sh_uri_r1_eff pres_sh_inc_r1_eff 
parties_seat hhi_cons_norm pct_myr_cons cons_sh_left cons_sh_para cons_sh_new 
cons_sh_lib cons_sh_cons sh_winner_gob gob_myr
peagrariac peofdereci pepsaludc
categoria homicidios faccion cons_com
;
#d cr
	
sort codmpio year
order codmpio year
xtset codmpio year

* Fill in missing muni names
replace municipio=municipio[_n-1] if codmpio==codmpio[_n-1]

compress

save "${dpwork}\PANEL MUNIS.dta", replace	
	
	